package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import ModelLayer.Club;

public class DBClub implements IFDBClub{
	private Connection con;
	public DBClub(){
		con = DBConnection.getInstance().getDBcon();
	}
	public Club findClubByID(int clubID) {
		String wClause = "clubID = "+clubID;
		System.out.println("Search club by its ID: "+ wClause);
		return singleWhere(wClause);
	}
	public Club findClubByName(String clubName) {
		String wClause = "name = '"+clubName+"'";
		System.out.println("Search club by its name: "+ wClause);
		return singleWhere(wClause);
	}
	public void insertlClub(Club club) throws SQLException {
		String query = "Insert into Club(clubID, name) values(" +
				club.getClubID()+",'" +
				club.getName()+"'";
		System.out.println("Insert query into club: "+ query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sql){
			System.out.println("SQLException inserting into club: "+sql);
			sql.printStackTrace();
		}
	}
	public void updateClub(Club club) throws SQLException {
		String query = "Update club set " +
				"name = '"+club.getName()+"'," +
				"where clubID = "+club.getClubID();
		System.out.println("Update query: "+query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sql){
			System.out.println("SQLException updating club: "+sql);
			sql.printStackTrace();
		}
	}
	private Club singleWhere(String wClause)
	{
		ResultSet results;
		Club clubObj = new Club();
	    String query =  buildQuery(wClause);
        System.out.println(query);
        
		try{
			
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			clubObj = buildClub(results);
		 		stmt.close();
			}
		}
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return clubObj;
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Club";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	private Club buildClub(ResultSet results){   
		Club clubObj = new Club();
        try{
        	clubObj.setClubID(results.getInt(1));
        	clubObj.setName(results.getString(2));
        }
       catch(Exception e)
       {
           System.out.println("error in building the customer object");
       }
       return clubObj;
    }
}